package com.anjie.powerproject.repository;

import com.anjie.powerproject.entity.PreStrategy;
import com.anjie.powerproject.entity.StrategyOutput;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

public interface StrategyOutputRepository extends JpaRepository<StrategyOutput,Integer> {
    @Query(nativeQuery = true, value = "select * from strategy_output where id in (select max(id) from strategy_output group by code,start_time,end_time,power,info,graph_id,strategy_type) and CAST(end_time as DATE)=CAST(?1 as DATE) and strategy_type=?2 and graph_id=?3")
    List<StrategyOutput> findStrategyOutputsByDateAndType(Date date, Integer type, Integer graphId);

    @Query(nativeQuery = true, value = "select * from strategy_output where id in (select max(id) from strategy_output group by code,start_time,end_time,power,info,graph_id,strategy_type) and strategy_type=?1 and graph_id=?2 and end_time>?3 and end_time<=?4")
    List<StrategyOutput> findStrategyOutputsByDateAndTypeAndEndTime(Integer type, Integer graphId,String yesterdayEndWorkTime,String endWorkTime);

    @Query(nativeQuery = true, value = "select * from strategy_output where id in (select max(id) from strategy_output group by code,start_time,end_time,power,info,graph_id,strategy_type) and (CAST(end_time as DATE)=CAST(?1 as DATE) or CAST(end_time as DATE)=CAST(?2 as DATE)) and strategy_type=?3 and graph_id=?4 and start_time>?5 and start_time<?6")
    List<StrategyOutput> findStrategyOutputsByDateAndTypeAndStartTime(Date today,Date yesterday, Integer type, Integer graphId,String yesterdayEndWorkTime,String endWorkTime);

    @Query(nativeQuery = true, value = "select * from strategy_output where start_time >= :start and end_time < :end")
    List<StrategyOutput> getStrategyOutput(@Param("start") String start, @Param("end") String end);

    @Query(nativeQuery = true, value = "select * from strategy_output where start_time >= :start and end_time < :end and strategy_type= :strategyType")
    List<StrategyOutput> getStrategyOutputByDateAndType(@Param("start") String start, @Param("end") String end, @Param("strategyType") Integer strategyType);

    @Query(nativeQuery = true, value = "select `power` from strategy_output where device_from = :device order by start_time desc limit :limit")
    List<Float> getPowerListByDeviceId(@Param("device") Integer device, @Param("limit") Integer limit);


    @Query(nativeQuery = true, value = "select * from strategy_output where ((DATE_FORMAT(start_time, '%Y-%m-%d') = :time and DATE_FORMAT(start_time, '%H') < 23) or (DATE_FORMAT(start_time, '%Y-%m-%d') = DATE_SUB(:time, INTERVAL 1 DAY) and DATE_FORMAT(start_time, '%H') >= 23))")
    List<StrategyOutput> getStrategyOutputByDate(@Param("time") String time);

    @Query(nativeQuery = true, value = "select * from strategy_output where graph_id = :graphId and ((DATE_FORMAT(start_time, '%Y-%m-%d') = :time and DATE_FORMAT(start_time, '%H') < 23) or (DATE_FORMAT(start_time, '%Y-%m-%d') = DATE_SUB(:time, INTERVAL 1 DAY) and DATE_FORMAT(start_time, '%H') >= 23))")
    List<StrategyOutput> getStrategyOutputByDateAndType(@Param("time") String time, @Param("graphId") Integer graphId);
}
